'''
1、多表关联：
    内连接：
        #没有on的限定条件，输出笛卡尔积模式
        select * from
        (select month, day from table_date where month is not null) as m1
        join
        (select month, day from table_date_1 where month  is not null) as m2
        on m1.month = m2.month
    外连接：
        #right join
        select * from
        (select month,day from table_date where month is not null) as m1
        right join
        (select month,day from table_date_1 where month  is not null) as m2
        on m1.month = m2.month

        #left join
        select * from
        (select month,day from table_date where month is not null) as m1
        left join
        (select month,day from table_date_1 where month  is not null) as m2
        on m1.month = m2.month

2、变量声明
    用户变量：默认类型为字符串，不需要事先声明或初始化；要有@
        set @greeting = "hello ,my friend!";
        select @greeting
    局部变量：要事先声明或初始化；没有@
    系统变量：需要有@@
        show variables;
        select @@character_set_database
存储过程
    定义：数据库中的函数，需要显示调用。应该先运行建立存储过程，然后运行调用。如果建立和调用一起运行会报错
    #建立一个加法计算器存储过程
        create procedure caculator(x int, y int)
        begin
            declare sum int;
            set sum = x+y;
            select sum;
        end
    #调用加法计算器
        call caculator(1, 2)
    #删除存储过程
        drop procedure caculator

3、触发器
    定义：嵌入在数据库中的函数，无需显示调用。
        create trigger date_check before insert on table_date for each row
        begin
            if new.month=12 and new.day=25 then set new.day="25, today is Christmas";
            end if;
        end
        insert into table_date(year, month, day) values (2020, 12, 25)
    查看触发器
    #show triggers from 库名；
    show triggers from tianya;
    删除触发器
    #drop trigger date_check

4、事务
    定义：事务提供一种要么全做, 要么不做的机制
    事务的特点：原子性、一致性、隔离、持久性
    事务的应用：（表的同步）
    ****************************************************************
        import random
        import pymysql
        #连接tianya数据库
        navicat_database_cfg = {
            'host': 'localhost',
            'user': 'root',
            'password': '123456',
            'database': 'tianya'
        }
        db_connect = pymysql.connect(**navicat_database_cfg)
        db_cursor = db_connect.cursor()


        def confirm_table():
            try:
                sql_1 = "insert into table_date(year, month, day) values (2020, 12, 9);"
                sql_2 = "insert into table_date_center(year, month, day) values (2020, 12, 9);"
                db_cursor.execute(sql_1)
                db_cursor.execute(sql_2)
            except Exception as e:
                print(e.args)
                db_connect.rollback()
            finally:
                db_connect.commit()


        if __name__ == "__main__":
            confirm_table()
        ******************************************************************
'''